package matlap.database.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.StudentDAO;
import matlap.shared.Score;
import matlap.shared.Student;
import matlap.shared.exceptions.MyException;

public class StudentJdbcDAO implements StudentDAO {

	Connection con = null;

	public int getStudentsNumber() {
		int nr = 0;
		String query = "SELECT COUNT(username) AS darab FROM people WHERE role=1";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return nr;
	}

	public List<String> getStudentsName() {
		List<String> list = new ArrayList<String>();
		String firstName;
		String lastName;
		String query = "SELECT first_name,last_name FROM people WHERE role=1 ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {
				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				list.add(i, lastName + " " + firstName);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<Student> getStudents() {
		List<Student> list = new ArrayList<Student>();
		String firstName;
		String lastName;
		String schoolName;
		String address;
		String userName;
		String locality;
		int classNr;
		String query = "SELECT last_name, first_name, people.username AS username, address, class, school_name, "
				+ "locality_name "
				+ "FROM people, students, school, locality "
				+ "WHERE people.username=students.username AND school.schoolID=students.schoolID AND "
				+ "locality.localityID= school.localityID AND role=1 ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				schoolName = rs.getString("school_name");
				address = rs.getString("address");
				userName = rs.getString("username");
				locality = rs.getString("locality_name");
				classNr = Integer.parseInt(rs.getString("class"));
				Student stu = new Student(firstName, lastName, userName, "",
						address, "", "", schoolName, locality, classNr);

				String query2 = "SELECT volume,score FROM scores WHERE username= '"
						+ userName + "'";
				try {
					Statement st2 = con.createStatement();
					ResultSet rs2 = st2.executeQuery(query2);
					while (rs2.next()) {
						int vol = rs2.getInt("volume");
						int score = rs2.getInt("score");
						stu.setScores(vol - 1, score);
					}

				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public List<Student> selectStudentsName() {

		List<Student> list = new ArrayList<Student>();
		String firstName;
		String lastName;
		String query = "SELECT last_name,first_name FROM people,students WHERE people.username=students.username ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				Student stu = new Student(firstName, lastName, null, null,
						null, null, null, null, null, 0);
				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<Student> selectSchools() {
		List<Student> list = new ArrayList<Student>();
		String schoolName;
		String query = "SELECT school_name FROM school ORDER BY school_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {
				schoolName = rs.getString("school_name");
				Student stu = new Student(null, null, null, null, null, null,
						null, schoolName, null, 0);
				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<Student> selectGrades() {
		List<Student> list = new ArrayList<Student>();
		int classNr;
		String query = "SELECT DISTINCT class FROM students";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {
				classNr = Integer.parseInt(rs.getString("class"));
				Student stu = new Student(null, null, null, null, null, null,
						null, null, null, classNr);
				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<Student> selectLocalities() {
		List<Student> list = new ArrayList<Student>();
		String localityName;
		String query = "SELECT locality_name FROM locality ORDER BY locality_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {
				localityName = rs.getString("locality_name");
				Student stu = new Student(null, null, null, null, null, null,
						null, null, localityName, 0);
				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<String> getUserNames() {

		List<String> list = new ArrayList<String>();
		String userName;
		String query = "SELECT username FROM people WHERE role=1 ORDER BY username";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {

				userName = rs.getString("username");
				list.add(i, userName);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/*
	 * getStudentByUserName2: returns the datas about a student, either he is
	 * registered or not
	 */
	public Student getStudentByUserName2(String userName) {
		String firstName;
		String lastName;
		String schoolName;
		String address;
		String localityName;
		int classNr;
		Student stud = null;
		String query = "SELECT last_name, first_name, people.address, class, school_name, locality_name "
				+ "FROM people, students, school, locality "
				+ "WHERE  people.username=students.username AND people.username ='"
				+ userName
				+ "' AND school.schoolID=students.schoolID AND school.localityID = locality.localityID AND role=1";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				schoolName = rs.getString("school_name");
				address = rs.getString("address");
				localityName = rs.getString("locality_name");
				classNr = Integer.parseInt(rs.getString("class"));
				stud = new Student(firstName, lastName, userName, "", address,
						"", "", schoolName, localityName, classNr);
				stud.setEmail("cim");
				stud.setPhone("tel");
			}

			String query2 = "SELECT volume,score FROM scores WHERE username= '"
					+ userName + "'";
			try {
				Statement st2 = con.createStatement();
				ResultSet rs2 = st2.executeQuery(query2);
				while (rs2.next()) {
					String vol = rs2.getString("volume");
					String score = rs2.getString("score");
					stud.setScores(Integer.parseInt(vol) - 1,
							Integer.parseInt(score));
				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return stud;
	}

	public Student getStudentByUserName(String userName) {
		String firstName;
		String lastName;
		String schoolName;
		String address;
		String localityName;
		int classNr;
		Student stud = null;
		String query = "SELECT last_name, first_name, people.address, class, school_name, locality_name,"
				+ "Email_address,phone_number "
				+ "FROM registered,people, students, school, locality "
				+ "WHERE people.username=students.username AND students.username=registered.username "
				+ "AND people.username = '"
				+ userName
				+ "' AND school.schoolID=students.schoolID "
				+ "AND school.localityID = locality.localityID AND role=1";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				schoolName = rs.getString("school_name");
				address = rs.getString("address");
				localityName = rs.getString("locality_name");
				classNr = Integer.parseInt(rs.getString("class"));
				System.out.println(Integer.parseInt(rs.getString("class"))
						+ "stuJDBC");
				stud = new Student(firstName, lastName, userName, "", address,
						"", "", schoolName, localityName, classNr);
				stud.setEmail(rs.getString("Email_address"));
				stud.setPhone(rs.getString("phone_number"));
				System.out.println(rs.getString("Email_address"));
			}

			String query2 = "SELECT volume,score FROM scores WHERE username= '"
					+ userName + "'";
			try {
				Statement st2 = con.createStatement();
				ResultSet rs2 = st2.executeQuery(query2);
				while (rs2.next()) {
					String vol = rs2.getString("volume");
					String score = rs2.getString("score");
					stud.setScores(Integer.parseInt(vol) - 1,
							Integer.parseInt(score));
				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return stud;
	}

	public Student getStudentPeopleByUserName(String userName) {
		String firstName;
		String lastName;
		String schoolName;
		String address;
		String localityName;
		int classNr;
		Student stud = null;
		String query = "SELECT last_name, first_name, people.address, class, school_name, locality_name "
				+ " FROM people, students, school, locality where "
				+ "people.username=students.username "
				+ "AND people.username = '"
				+ userName
				+ "' AND school.schoolID=students.schoolID "
				+ "AND school.localityID = locality.localityID AND role=1";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				schoolName = rs.getString("school_name");
				address = rs.getString("address");
				localityName = rs.getString("locality_name");
				classNr = Integer.parseInt(rs.getString("class"));
				stud = new Student(firstName, lastName, userName, "", address,
						"", "", schoolName, localityName, classNr);

			}

			String query2 = "SELECT volume,score FROM scores where username= '"
					+ userName + "'";
			try {
				Statement st2 = con.createStatement();
				ResultSet rs2 = st2.executeQuery(query2);
				while (rs2.next()) {
					String vol = rs2.getString("volume");
					String score = rs2.getString("score");
					stud.setScores(Integer.parseInt(vol) - 1,
							Integer.parseInt(score));
				}
			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return stud;
	}

	public void updatePeopleStudents(String userName, String lastName,
			String firstName, String address, String schoolName, int classNr) throws MyException {

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = "";

			if (lastName != "") {
				s = new String("UPDATE people SET last_name='" + lastName
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (firstName != "") {
				s = new String("UPDATE people SET first_name='" + firstName
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (address != "") {
				s = new String("UPDATE people SET address='" + address
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			

			if (schoolName != "") {
				int id = selectSchoolID(schoolName);
				updateStudents(userName, id, classNr);
			} else {
				updateStudents(userName, 0, classNr);
			}
			
		} catch (SQLException ex) {
			throw new MyException("Nem sikerült a felvezetés. Ellenőrizd az adatokat és probálkozz újra!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem sikerült a felvezetés. Ellenőrizd az adatokat és probálkozz újra!");
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}
	}

	public void updateStudents(String userName, int schoolID, int classNr) throws MyException{
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			if (schoolID != 0) {
				String s = new String("UPDATE students SET schoolID='"
						+ schoolID + "' WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (classNr != 0) {
				String s = new String("UPDATE students SET class='" + classNr
						+ "' WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
		} catch (SQLException ex) {
			throw new MyException("Nem sikerült a frissités. Ellenőrizd az adatokat és probálkozz újra!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem sikerült a frissités. Ellenőrizd az adatokat és probálkozz újra!");

		}

	}

	public void deleteStudent(String userName) throws MyException {

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = new String("DELETE FROM scores WHERE username='"
					+ userName + "'");
			st.executeUpdate(s);
			s = new String("DELETE FROM students WHERE username='" + userName
					+ "'");
			st.executeUpdate(s);
			s = new String("DELETE FROM tasks WHERE username='" + userName
					+ "'");
			st.executeUpdate(s);
			s = new String("DELETE FROM registered WHERE username='" + userName
					+ "'");
			st.executeUpdate(s);
			s = new String("DELETE FROM people WHERE username='" + userName
					+ "'");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			throw new MyException("Nem lehet törölni!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem lehet törölni");
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}

	}

	public void update(Student student) throws MyException{
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = new String("UPDATE people SET last_name='"
					+ student.getLastName() + "'WHERE username='"
					+ student.getUserName() + "'");
			st.execute(s);

			s = new String("UPDATE people SET first_name='"
					+ student.getFirstName() + "'WHERE username='"
					+ student.getUserName() + "'");
			st.executeUpdate(s);

			s = new String("UPDATE people SET address='" + student.getAddress()
					+ "'WHERE username='" + student.getUserName() + "'");
			st.executeUpdate(s);

			s = new String("UPDATE registered SET address='"
					+ student.getAddress() + "'WHERE username='"
					+ student.getUserName() + "'");
			st.executeUpdate(s);

			s = new String("UPDATE registered SET Email_address='"
					+ student.getEmail() + "'WHERE username='"
					+ student.getUserName() + "'");
			st.executeUpdate(s);

			s = new String("UPDATE registered SET phone_number='"
					+ student.getPhone() + "'WHERE username='"
					+ student.getUserName() + "'");
			st.executeUpdate(s);

			Hash hash = new Hash();
			String password = hash.hashPassword(student.getPassword());

			s = new String("UPDATE registered SET password='" + password
					+ "'WHERE username='" + student.getUserName() + "'");
			st.executeUpdate(s);

			s = new String("UPDATE students SET schoolID='"
					+ selectSchoolID(student.getSchool()) + "'WHERE username='"
					+ student.getUserName() + "'");
			st.executeUpdate(s);

			s = new String("UPDATE students SET class='" + student.getClassNr()
					+ "'WHERE username='" + student.getUserName() + "'");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			throw new MyException("Nem sikerült a frissités. Ellenőrizd az adatokat és probálkozz újra!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem sikerült a frissités. Ellenőrizd az adatokat és probálkozz újra!");
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}

	}

	public int selectSchoolID(String schoolName) {
		String query = "SELECT schoolID FROM school WHERE school_name='"
				+ schoolName + "'";
		int n = 0;
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				n = rs.getInt("schoolID");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return n;
	}

	public List<Student> getStudentsBy(String studentName, String schoolName,
			String grade, String localityName) {

		String[] n = studentName.split(" ");
		List<Student> list = new ArrayList<Student>();
		String firstName;
		String lastName;
		String school;
		String address;
		String userName;
		String locality;
		int classNr;
		String query = "";

		if (studentName.length() == 0) {
			if (schoolName.length() == 0) {
				if (grade.length() == 0) {
					if (localityName.length() == 0) {
						// nincs semmi kijelolve
						query = "SELECT last_name, first_name, people.username as username, address, class, "
								+ "school_name, locality_name FROM people, students, school, locality WHERE "
								+ "people.username=students.username AND school.schoolID=students.schoolID "
								+ "AND locality.localityID= school.localityID AND role=1 ORDER BY last_name, first_name";

					} else
					// csak a helyiseg van kijelolve
					{
						query = "SELECT last_name, first_name, people.username as username, address, class, "
								+ "school_name, locality_name "
								+ "FROM people, students, school, locality WHERE "
								+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 AND locality_name like '%"
								+ localityName
								+ "%' ORDER BY last_name, first_name";
					}
				} else if (localityName.length() == 0) {
					// csak osztaly van kijelolve
					query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
							+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 AND class='"
							+ grade + "' ORDER BY last_name, first_name";

				} else {
					// helyiseg es osztaly van kijelolve
					query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
							+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1"
							+ " AND class='"
							+ grade
							+ "' AND locality_name like '%"
							+ localityName
							+ "%' ORDER BY last_name, first_name";
				}
			} else if ((grade.length() == 0) && (localityName.length() == 0)) {
				// csak iskola
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 AND school_name like '%"
						+ schoolName + "%' ORDER BY last_name, first_name";
			} else if ((grade.length() != 0) && (localityName.length() == 0)) {
				// iskola es osztaly
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ "AND school_name like '%"
						+ schoolName
						+ "%' AND class='"
						+ grade
						+ "' ORDER BY last_name, first_name";
			} else if ((grade.length() == 0) && (localityName.length() != 0)) {
				// iskola es helyseg
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ "AND school_name like '%"
						+ schoolName
						+ "%'AND locality_name like '%"
						+ localityName
						+ "%' ORDER BY last_name, first_name";
			} else if ((grade.length() != 0) && (localityName.length() != 0)) {
				// iskola es osztaly es helyseg
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND school_name like '%"
						+ schoolName
						+ "%'AND class='"
						+ grade
						+ "' AND locality_name like '%"
						+ localityName
						+ "%' ORDER BY last_name, first_name";
			}

		} else if ((grade.length() == 0) && (localityName.length() == 0)
				&& (schoolName.length() == 0)) {
			// csak nev
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ " (people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 ) AND (( last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%' ) OR ( last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%' ) ) ORDER BY last_name, first_name";

			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1) AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName + "%')) ORDER BY last_name, first_name";
		} else if ((grade.length() != 0) && (localityName.length() == 0)
				&& (schoolName.length() == 0)) {
			// nev es osztaly
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND class='"
						+ grade
						+ "') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1"
						+ "AND class='"
						+ grade
						+ "') AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName
						+ "%')) ORDER BY last_name, first_name";

		} else if ((grade.length() != 0) && (localityName.length() != 0)
				&& (schoolName.length() == 0)) {
			// nev osztaly helyseg
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND class='"
						+ grade
						+ "' AND locality_name like '%"
						+ localityName
						+ "%') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1"
						+ " AND class='"
						+ grade
						+ "' AND locality_name like '%"
						+ localityName
						+ "%' ) AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName
						+ "%')) ORDER BY last_name, first_name";

		} else if ((grade.length() != 0) && (localityName.length() == 0)
				&& (schoolName.length() != 0)) {
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1"
						+ " AND class='"
						+ grade
						+ "' AND school_name like '%"
						+ schoolName
						+ "%') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1"
						+ " AND class='"
						+ grade
						+ "' AND school_name like '%"
						+ schoolName
						+ "%') AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName + "%')) ORDER BY last_name, first_name";

		} else if ((grade.length() != 0) && (localityName.length() != 0)
				&& (schoolName.length() != 0)) {
			// nev osztaly helyseg iskola
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ " ( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND class='"
						+ grade
						+ "' AND locality_name like '%"
						+ localityName
						+ "%' AND school_name like '%"
						+ schoolName
						+ "%') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ " ( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND class='"
						+ grade
						+ "' AND locality_name like '%"
						+ localityName
						+ "%' AND school_name like  '%"
						+ schoolName
						+ "%') AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName
						+ "%')) ORDER BY last_name, first_name";

		} else if ((grade.length() == 0) && (localityName.length() != 0)
				&& (schoolName.length() == 0)) {
			// nev helyseg
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "(people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND locality_name like '%"
						+ localityName
						+ "%') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND locality_name like '%"
						+ localityName
						+ "%') AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName
						+ "%')) ORDER BY last_name, first_name";

		} else if ((grade.length() == 0) && (localityName.length() != 0)
				&& (schoolName.length() != 0)) {
			// nev helyseg iskola
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND locality_name like '%"
						+ localityName
						+ "%' AND school_name like '%"
						+ schoolName
						+ "%') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ "( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND locality_name like '%"
						+ localityName
						+ "%' AND school_name like '%"
						+ schoolName
						+ "%') AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName
						+ "%')) ORDER BY last_name, first_name";

		} else if ((grade.length() == 0) && (localityName.length() == 0)
				&& (schoolName.length() != 0)) {
			// nev iskola
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ " (people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND school_name like '%"
						+ schoolName
						+ "%') AND ((last_name LIKE '%"
						+ n[1]
						+ "%' AND first_name LIKE '%"
						+ n[0]
						+ "%') OR (last_name LIKE '%"
						+ n[0]
						+ "%' AND first_name LIKE '%"
						+ n[1]
						+ "%')) ORDER BY last_name, first_name";
			else
				query = "SELECT last_name, first_name, people.username as username, address, class, school_name, locality_name FROM people, students, school, locality WHERE "
						+ " ( people.username=students.username AND school.schoolID=students.schoolID AND locality.localityID= school.localityID AND role=1 "
						+ " AND school_name like '%"
						+ schoolName
						+ "%') AND ( ( last_name LIKE '%"
						+ studentName
						+ "%') OR (first_name LIKE '%"
						+ studentName
						+ "%')) ORDER BY last_name, first_name";

		}
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				school = rs.getString("school_name");
				address = rs.getString("address");
				userName = rs.getString("username");
				locality = rs.getString("locality_name");
				classNr = Integer.parseInt(rs.getString("class"));
				Student stu = new Student(firstName, lastName, userName, "",
						address, "", "", school, locality, classNr);

				String query2 = "SELECT volume,score FROM scores WHERE username= '"
						+ userName + "'";
				try {
					Statement st2 = con.createStatement();
					ResultSet rs2 = st2.executeQuery(query2);
					while (rs2.next()) {
						String vol = rs2.getString("volume");
						String score = rs2.getString("score");
						stu.setScores(Integer.parseInt(vol) - 1,
								Integer.parseInt(score));
					}
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public void insertStudentScore(Score s) {
		String query = "INSERT INTO scores VALUES('" + s.getUserName() + "','"
				+ s.getVolume() + "','" + s.getScore() + "')";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			st.executeUpdate(query);
			
			setCorrected(s.getUserName(),s.getVolume());
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void setCorrected(String username, int volume){
		String query = "UPDATE tasks SET Corected=1 WHERE username='"+username+"' and volume='"+volume+"'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			st.executeUpdate(query);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
	}
	public void updateStudentScore(Score s) throws MyException{
		String query = "update scores set score=" + s.getScore()
				+ " WHERE username ='" + s.getUserName() + "' AND volume= '"
				+ s.getVolume() + "'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			st.executeUpdate(query);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public List<Score> selectStudentsScore() {
		List<Score> list = new ArrayList<Score>();
		String username = "";
		int score = 0;
		String query = "SELECT scores.username,sum(score) AS ertek,first_name,last_name FROM scores,people WHERE scores.username=people.username GROUP BY username ORDER BY ertek DESC ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;
			while (rs.next()) {
				username = rs.getString("username");
				score = rs.getInt("ertek");
				System.out.println(username + " " + score);
				Score sco = new Score(username, 0, score);
				sco.setName(rs.getString("last_name") + " "
						+ rs.getString("first_name"));
				list.add(i, sco);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<Student> selectStudentsWithoutScore() {
		List<Student> list = new ArrayList<Student>();
		String query = "SELECT first_name,last_name FROM students,people WHERE students.username=people.username AND NOT exists (SELECT * FROM scores WHERE students.username = scores.username) ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {
				Student student = new Student();
				student.setFirstName(rs.getString("first_name"));
				student.setLastName(rs.getString("last_name"));
				list.add(i, student);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

}
